const { DataTypes } = require('sequelize')

module.exports = (sequelize) => {
  const AISQLLog = sequelize.define('AISQLLog', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    user_id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: {
        model: 'users',
        key: 'id'
      },
      comment: '执行用户ID'
    },
    session_id: {
      type: DataTypes.STRING,
      allowNull: true,
      comment: '聊天会话ID'
    },
    user_query: {
      type: DataTypes.TEXT,
      allowNull: false,
      comment: '用户原始请求'
    },
    ai_response: {
      type: DataTypes.TEXT,
      allowNull: true,
      comment: 'AI响应内容'
    },
    sql_statement: {
      type: DataTypes.TEXT,
      allowNull: true,
      comment: '执行的SQL语句'
    },
    sql_type: {
      type: DataTypes.ENUM('SELECT', 'INSERT', 'UPDATE', 'DELETE', 'CREATE', 'ALTER', 'DROP', 'OTHER'),
      allowNull: true,
      comment: 'SQL语句类型'
    },
    target_table: {
      type: DataTypes.STRING,
      allowNull: true,
      comment: '目标数据表名'
    },
    execution_status: {
      type: DataTypes.ENUM('pending', 'success', 'failed', 'cancelled'),
      allowNull: false,
      defaultValue: 'pending',
      comment: '执行状态'
    },
    execution_time: {
      type: DataTypes.INTEGER,
      allowNull: true,
      comment: '执行耗时(毫秒)'
    },
    rows_affected: {
      type: DataTypes.INTEGER,
      allowNull: true,
      comment: '影响行数'
    },
    error_message: {
      type: DataTypes.TEXT,
      allowNull: true,
      comment: '错误信息'
    },
    execution_result: {
      type: DataTypes.JSON,
      allowNull: true,
      comment: '执行结果数据(JSON格式)'
    },
    risk_level: {
      type: DataTypes.ENUM('low', 'medium', 'high', 'critical'),
      allowNull: false,
      defaultValue: 'low',
      comment: '风险等级'
    },
    is_approved: {
      type: DataTypes.BOOLEAN,
      allowNull: false,
      defaultValue: false,
      comment: '是否已审批'
    },
    approved_by: {
      type: DataTypes.INTEGER,
      allowNull: true,
      references: {
        model: 'users',
        key: 'id'
      },
      comment: '审批人ID'
    },
    approved_at: {
      type: DataTypes.DATE,
      allowNull: true,
      comment: '审批时间'
    },
    ip_address: {
      type: DataTypes.STRING,
      allowNull: true,
      comment: '客户端IP地址'
    },
    user_agent: {
      type: DataTypes.TEXT,
      allowNull: true,
      comment: '用户代理信息'
    },
    ai_model: {
      type: DataTypes.STRING,
      allowNull: true,
      defaultValue: 'deepseek-chat',
      comment: '使用的AI模型'
    },
    token_usage: {
      type: DataTypes.INTEGER,
      allowNull: true,
      defaultValue: 0,
      comment: 'Token消耗'
    },
    created_at: {
      type: DataTypes.DATE,
      defaultValue: DataTypes.NOW,
      comment: '创建时间'
    },
    updated_at: {
      type: DataTypes.DATE,
      defaultValue: DataTypes.NOW,
      comment: '更新时间'
    },
    executed_at: {
      type: DataTypes.DATE,
      allowNull: true,
      comment: 'SQL实际执行时间'
    }
  }, {
    tableName: 'ai_sql_logs',
    timestamps: true,
    createdAt: 'created_at',
    updatedAt: 'updated_at',
    comment: 'AI中控SQL执行日志表',
    indexes: [
      {
        fields: ['user_id']
      },
      {
        fields: ['session_id']
      },
      {
        fields: ['execution_status']
      },
      {
        fields: ['sql_type']
      },
      {
        fields: ['target_table']
      },
      {
        fields: ['risk_level']
      },
      {
        fields: ['created_at']
      },
      {
        fields: ['user_id', 'created_at']
      }
    ]
  })

  // 关联关系
  AISQLLog.associate = (models) => {
    // 执行用户
    AISQLLog.belongsTo(models.User, {
      foreignKey: 'user_id',
      as: 'executor'
    })
    
    // 审批用户
    AISQLLog.belongsTo(models.User, {
      foreignKey: 'approved_by',
      as: 'approver'
    })
  }

  return AISQLLog
}
